package com.uncp.comisiondeadmision.carnet.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.uncp.comisiondeadmision.comprobantedepago.beans.PostulanteBean;
import com.uncp.comisiondeadmision.util.MySqlDBConn;

public class MySqlPostulanteCarnetDAO implements PostulanteCarnetDAO {

	public ArrayList<PostulanteBean> consultaActivos() throws SQLException {

		Connection con = null;
		Statement s = null;
		ResultSet rs = null;
		String pendiente = "Pendiente";
		String aprobado = "aprobado";
		ArrayList<PostulanteBean> array = new ArrayList<PostulanteBean>();

		try {
			con = new MySqlDBConn().getConnection();
			s = con.createStatement();
			String sql = " select p.codpostulante,p.nombres,p.apepat,p.apemat,p.estado,p.dni "
					+ "  from postulante p join solicitud s "
					+ " on p.CODPOSTULANTE = s.CODPOSTULANTE "
					+ "  where p.ESTADO = '"
					+ pendiente
					+ "' and  s.ESTADO = '" + aprobado + "' ";
			rs = s.executeQuery(sql);

			System.out.println("lista cliente :" + sql);
			PostulanteBean bean;

			while (rs.next()) {

				bean = new PostulanteBean();
				bean.setIntCodPostulante(rs.getInt(1));
				bean.setStrNombres(rs.getString(2));
				bean.setStrApepat(rs.getString(3));
				bean.setStrApemat(rs.getString(4));
				bean.setStrEstado(rs.getString(5));
				bean.setStrDni(rs.getString(6));
				array.add(bean);

			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (con != null)
					con.close();
				if (s != null)
					s.close();
				if (rs != null)
					rs.close();
			} catch (Exception e2) {
				System.out.println(e2.getMessage());
			}
		}

		return array;
	}

	public PostulanteBean consultaXCodigo(int Codigo, String estado)
			throws SQLException {

		Connection con = null;
		Statement s = null;
		ResultSet rs = null;
		String aprobado = "aprobado";
		PostulanteBean bean = null;
		try {

			con = new MySqlDBConn().getConnection();

			String sql = "select p.codpostulante, p.nombres,p.apepat,p.apemat,p.estado,p.foto"
					+ " from postulante p join solicitud s "
					+ " on p.CODPOSTULANTE = s.CODPOSTULANTE"
					+ "  where p.codpostulante='"
					+ Codigo
					+ "' and  p.ESTADO='"
					+ estado
					+ "' and  s.ESTADO='"
					+ aprobado + "'";

			s = con.createStatement();
			rs = s.executeQuery(sql);
			System.out.println("Consulta x Codigo :" + sql);
			while (rs.next()) {

				bean = new PostulanteBean();
				bean.setIntCodPostulante(rs.getInt(1));
				bean.setStrNombres(rs.getString(2));
				bean.setStrApemat(rs.getString(3));
				bean.setStrApemat(rs.getString(4));
				bean.setStrEstado(rs.getString(5));
				bean.setBlobImagen(rs.getBytes(6));

			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {

				if (con != null)
					con.close();
				if (s != null)
					s.close();
				if (rs != null)
					rs.close();

			} catch (Exception e2) {
				System.out.println(e2.getMessage());
			}
		}

		return bean;
	}

	public boolean actualizaEstado(int codigoPostu) throws SQLException {

		Connection con = null;
		Statement s = null;
		String actualizarEstado = "Activo";
		try {
			con = new MySqlDBConn().getConnection();
			String sql = "update postulante set ESTADO ='" + actualizarEstado
					+ "' where CODPOSTULANTE ='" + codigoPostu + "'";
			s = con.createStatement();

			System.out.println("ActulizaEstado :" + sql);

			return s.execute(sql);

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {

				if (con != null)
					con.close();
				if (s != null)
					s.close();

			} catch (Exception e2) {
				System.out.println(e2.getMessage());
			}
		}

		return false;
	}

	@Override
	public ArrayList<PostulanteBean> consultaPendiente() throws SQLException {

		Connection con = null;
		Statement s = null;
		ResultSet rs = null;
		String pendiente = "pendiente";
		
		ArrayList<PostulanteBean> array = new ArrayList<PostulanteBean>();

		try {
			con = new MySqlDBConn().getConnection();
			s = con.createStatement();
			String sql = " select p.codpostulante,p.nombres,p.apepat,p.apemat,p.estado "
					+ "  from postulante p join solicitud s "
					+ " on p.CODPOSTULANTE = s.CODPOSTULANTE "
					+ "  where p.ESTADO = '"
					+ pendiente
					+ "' and  s.ESTADO = '" + pendiente + "' ";
			rs = s.executeQuery(sql);

			System.out.println("lista cliente :" + sql);
			PostulanteBean bean;

			while (rs.next()) {

				bean = new PostulanteBean();
				bean.setIntCodPostulante(rs.getInt(1));
				bean.setStrNombres(rs.getString(2));
				bean.setStrApepat(rs.getString(3));
				bean.setStrApemat(rs.getString(4));
				bean.setStrEstado(rs.getString(5));
				array.add(bean);

			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (con != null)
					con.close();
				if (s != null)
					s.close();
				if (rs != null)
					rs.close();
			} catch (Exception e2) {
				System.out.println(e2.getMessage());
			}
		}

		return array;
	}

}
